IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[MapArraAgreementData]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[MapArraAgreementData]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
  
CREATE Procedure [dbo].[MapArraAgreementData] 
/*
**	Maps all the Arra proposal/agreement/subcontractor/vendor data from InfoEd
*/
As
Begin

Set nocount on

Begin Try

/*-----------------------------------------------------------------------------
Compute the current quarter.  First find the minimum quarter end
date in the ARRA Agreement data that is flagged as final report = 0.  
-----------------------------------------------------------------------------*/
declare @QuarterEndDate datetime
select @QuarterEndDate = min(QuarterEndDate) from [dbo].[ArraQuarterlyReportStatus] where IsFinalReport = 0

/*-----------------------------------------------------------------------------
If there are no ARRA agreement report status rows flagged as not final, 
calculate the previous calendar quarter end date.
-----------------------------------------------------------------------------*/
declare @todaysDate datetime
declare @Year int
declare @Month int

if @QuarterEndDate is null begin
	Raiserror('Quarterly reporting status row that is flagged incomplete was not found!', 16, 1);
	return;
End
--select @QuarterEndDate

/*-----------------------------------------------------------------------------------
Map the Quarterly Reporting Status Row
-----------------------------------------------------------------------------------*/
UPDATE [dbo].[Stg_IE_ARRA_ProposalData] 
SET ArraQuarterlyReportStatusID = (Select ID 
									from [dbo].[ArraQuarterlyReportStatus] 
									where QuarterEndDate = @QuarterEndDate)

/*-----------------------------------------------------------------------------------
EXCLUDE any agreements that have an award date that is after the quarter end date
-----------------------------------------------------------------------------------*/
delete sc from [dbo].[Stg_IE_ARRA_ProposalData] sp
inner join Agragreement_fct af on af.FundID = sp.FundID
left outer join [dbo].[Stg_IE_ARRA_SubContractorData] sc on sc.InfoEdProposalNumber = sp.InfoEdProposalNumber 
															and sc.PSAccountNumber = sp.PSAccountNumber
where coalesce(sp.[NogaIssueDate],af.ProjectStartDate)> @QuarterEndDate

delete sp from [dbo].[Stg_IE_ARRA_ProposalData] sp
inner join Agragreement_fct af on af.FundID = sp.FundID
where coalesce(sp.[NogaIssueDate],af.ProjectStartDate)> @QuarterEndDate

/*-----------------------------------------------------------------------------------
EDIT bad State Codes.  InfoEd returns DNA if the data is not applicable.  This is too
long for the Gov requirements, so we will translate it as NA.
-----------------------------------------------------------------------------------*/
update s set primaryPerformanceState = 'NA' 
from [dbo].[Stg_IE_ARRA_ProposalData] s where s.primaryPerformanceState = 'DNA'

update s set RecipientState = 'NA' from [dbo].[Stg_IE_ARRA_ProposalData] s where s.RecipientState = 'DNA'

update s set subprimaryPerformanceState = 'NA' 
from [dbo].[Stg_IE_ARRA_SubContractorData] s where s.subprimaryPerformanceState = 'DNA'

/*-----------------------------------------------------------------------------------
Map the fund ID in staging
-----------------------------------------------------------------------------------*/
UPDATE s SET FundID = f.ID
from [dbo].[Stg_IE_ARRA_ProposalData] s 
inner join agrfund f on f.FundNumber = s.PSAccountNumber

/*-----------------------------------------------------------------------------------
Map the Pre-award administrator in staging
-----------------------------------------------------------------------------------*/
UPDATE s SET PreAwardManagerAdmPersonID = p.ID
from [dbo].[Stg_IE_ARRA_ProposalData] s 
inner join fwkdomainuser fdu on fdu.username = s.PreAwardManagerUserID
inner join AdmPerson p on p.fwkdomainuserid = fdu.id

/*-----------------------------------------------------------------------------------
Map the Department administrator in staging
-----------------------------------------------------------------------------------*/
UPDATE s SET DepartmentManagerAdmPersonID = p.ID
from [dbo].[Stg_IE_ARRA_ProposalData] s 
inner join fwkdomainuser fdu on fdu.username = s.DepartmentManagerUserID
inner join AdmPerson p on p.fwkdomainuserid = fdu.id

declare @MaxID int
/*-----------------------------------------------------------------------------------
Seed New Recipients in staging
-----------------------------------------------------------------------------------*/
if object_id('tempdb..#NewCodes') is not null drop table #NewCodes
Create table #NewCodes(ID int identity, code varchar(20))

INSERT INTO #NewCodes([Code])
Select distinct s.RecipientDunsNumber From [dbo].[Stg_IE_ARRA_ProposalData] s
left outer join [dbo].[ArraRecipient] a on a.DunsNumber = replace(s.RecipientDunsNumber,'-','')
where a.id is null and IsNull(s.RecipientDunsNumber,'')<>''

set @MaxID = isnull((select max(ID) from [dbo].[ArraRecipient]),0)
Update s Set NewArraRecipientID = @MaxID + t.ID
from [dbo].[Stg_IE_ARRA_ProposalData] s
inner join #NewCodes t on t.code = s.RecipientDunsNumber

/*-----------------------------------------------------------------------------------
Seed New SUB Recipients in staging
-----------------------------------------------------------------------------------*/
set @MaxID = @MaxID + isnull((select max(ID) from #NewCodes),0)
truncate table #NewCodes

INSERT INTO #NewCodes([Code])
Select distinct s.SubRecipientDunsNumber From [dbo].[Stg_IE_ARRA_SubContractorData] s
left outer join [dbo].[ArraRecipient] a on a.DunsNumber = replace(s.SubRecipientDunsNumber,'-','')
where a.id is null and IsNull(s.SubRecipientDunsNumber,'')<>''

Update s Set NewArraRecipientID = @MaxID + t.ID
from [dbo].[Stg_IE_ARRA_SubContractorData] s
inner join #NewCodes t on t.code = s.SubRecipientDunsNumber

/*-----------------------------------------------------------------------------------
Seed New Government Contract Office Codes in staging
-----------------------------------------------------------------------------------*/
truncate table #NewCodes

INSERT INTO #NewCodes([Code])
Select distinct s.GovernmentContractOfficeCode
From [dbo].[Stg_IE_ARRA_ProposalData] s
left outer join [dbo].[ArraGovContractOffice] a on a.code = s.GovernmentContractOfficeCode
where a.code is null and isnull(s.GovernmentContractOfficeCode,'')<>''

set @MaxID = isnull((select max(ID) from [dbo].[ArraGovContractOffice]),0)
Update s Set NewArraGovContractOfficeID = @MaxID + t.ID
from [dbo].[Stg_IE_ARRA_ProposalData] s
inner join #NewCodes t on t.code = s.GovernmentContractOfficeCode

/*-----------------------------------------------------------------------------------
Seed New Primary Performance Locations in staging
-----------------------------------------------------------------------------------*/
if object_id('tempdb..#NewLocations') is not null drop table #NewLocations
Create table #NewLocations(ID int identity, street1 varchar(55), street2 varchar(55), City varchar(35), [state] varchar(2), zip varchar(12), Country varchar(2), CongressionalDistrict varchar(2))

INSERT INTO #NewLocations(street1, street2, City, [State], Zip, Country, CongressionalDistrict)
Select distinct s.PrimaryPerformanceStreet1,s.PrimaryPerformanceStreet2,s.PrimaryPerformanceCity
	,s.PrimaryPerformanceState, s.PrimaryPerformanceZip, coalesce(left(s.PrimaryPerformanceCountry,2),''), coalesce(right(s.PrimaryPerformanceCongressDistrict,2),'')
From [dbo].[Stg_IE_ARRA_ProposalData] s
left outer join [dbo].[ArraPrimaryPerformanceLocation] a on coalesce(a.[StreetAddress1],'') = coalesce(s.PrimaryPerformanceStreet1,'')
						and coalesce(a.[StreetAddress2],'') = coalesce(s.PrimaryPerformanceStreet2,'')
						and a.[City] = s.PrimaryPerformanceCity 
						and a.[State] = s.PrimaryPerformanceState
						and coalesce(a.[Zip],'') = coalesce(replace(s.PrimaryPerformanceZip,'-',''),'') 
						and coalesce(a.[Country],'') = coalesce(left(s.PrimaryPerformanceCountry,2),'')
						and coalesce(a.[CongressionalDistrict],'') =  coalesce(right(s.PrimaryPerformanceCongressDistrict,2),'')
where a.[City] is null and isnull(s.PrimaryPerformanceCity,'')<>''

set @MaxID = isnull((select max(ID) from [dbo].[ArraPrimaryPerformanceLocation]),0)
Update s Set NewArraPrimaryPerformanceLocationID = @MaxID + t.ID
from [dbo].[Stg_IE_ARRA_ProposalData] s
inner join #NewLocations t on coalesce(s.PrimaryPerformanceStreet1,'') = coalesce(t.Street1,'') and coalesce(s.PrimaryPerformanceStreet2,'') = coalesce(t.Street2,'')
					 and s.PrimaryPerformanceCity = t.City and s.PrimaryPerformanceState = t.[State]
					 and  coalesce(s.PrimaryPerformanceZip,'') = coalesce(t.Zip ,'')
					 and coalesce(t.[Country],'') = coalesce(left(s.PrimaryPerformanceCountry,2),'')
					 and coalesce(t.[CongressionalDistrict],'') =  coalesce(right(s.PrimaryPerformanceCongressDistrict,2),'')

--Set the new maxID for continuation of Sub Primary Performance Location seeding.
set @MaxID = @MaxID + coalesce((select max(ID) from #NewLocations),0)
drop table #NewLocations

/*-----------------------------------------------------------------------------------
Seed New Sub Primary Performance Locations in staging
-----------------------------------------------------------------------------------*/
if object_id('tempdb..#NewSubLocations') is not null drop table #NewSubLocations
Create table #NewSubLocations(ID int identity, street1 varchar(55), street2 varchar(55), City varchar(35), [state] varchar(2), zip varchar(12), Country varchar(2), CongressionalDistrict varchar(2))

INSERT INTO #NewSubLocations(street1, street2, City, [State], Zip, Country, CongressionalDistrict)
Select distinct s.SubPrimaryPerformanceAddress1,s.SubPrimaryPerformanceAddress2,s.SubPrimaryPerformanceCity
	,s.SubPrimaryPerformanceState, s.SubPrimaryPerformanceZip, coalesce(left(s.SubPrimaryPerformanceCountry,2),''), coalesce(right(s.SubPrimaryPerformanceCongressDistrict,2),'')
From [dbo].[Stg_IE_ARRA_SubContractorData] s
left outer join [dbo].[ArraPrimaryPerformanceLocation] a on coalesce(a.[StreetAddress1],'') = coalesce(s.SubPrimaryPerformanceAddress1,'')
						and coalesce(a.[StreetAddress2],'') = coalesce(s.SubPrimaryPerformanceAddress2,'')
						and a.[City] = s.SubPrimaryPerformanceCity and a.[State] = s.SubPrimaryPerformanceState
						and coalesce(a.[Zip],'') = coalesce(replace(s.SubPrimaryPerformanceZip,'-',''),'') 
						and coalesce(a.[Country],'') = coalesce(left(s.SubPrimaryPerformanceCountry,2),'')
						and coalesce(a.[CongressionalDistrict],'') =  coalesce(right(s.SubPrimaryPerformanceCongressDistrict,2),'')
where a.[City] is null and isnull(s.SubPrimaryPerformanceCity,'')<>''

Update s Set NewArraSubPrimaryPerformanceLocationID = @MaxID + t.ID
from [dbo].[Stg_IE_ARRA_SubContractorData] s
inner join #NewSubLocations t on coalesce(s.SubPrimaryPerformanceAddress1,'') = coalesce(t.Street1,'') and coalesce(s.SubPrimaryPerformanceAddress2,'') = coalesce(t.Street2,'')
					 and s.SubPrimaryPerformanceCity = t.City and s.SubPrimaryPerformanceState = t.[State]
					 and  coalesce(s.SubPrimaryPerformanceZip,'') = coalesce(t.Zip ,'')
					 and coalesce(t.[Country],'') = coalesce(left(s.SubPrimaryPerformanceCountry,2),'')
					 and coalesce(t.[CongressionalDistrict],'') =  coalesce(right(s.SubPrimaryPerformanceCongressDistrict,2),'')

drop table #NewSubLocations


/*-----------------------------------------------------------------------------------
Seed New ArraAgreements in staging
-----------------------------------------------------------------------------------*/
if object_id('tempdb..#NewAgreements') is not null drop table #NewAgreements
Create table #NewAgreements(ID int identity, foldernumber varchar(12), FundID int)

INSERT INTO #NewAgreements(foldernumber, FundID)
Select distinct s.InfoEdProposalNumber,s.FundID
from [dbo].[Stg_IE_ARRA_ProposalData] s
left outer join [dbo].[ArraAgreement] a on a.foldernumber = s.InfoEdProposalNumber 
									and a.FundID = s.FundID
									and a.ArraQuarterlyReportStatusID = s.ArraQuarterlyReportStatusID
where a.id is null and s.fundID is not null
									
set @MaxID = isnull((select max(ID) from [dbo].[ArraAgreement]),0)
Update s Set [NewArraAgreementID] = @MaxID + t.ID
from [dbo].[Stg_IE_ARRA_ProposalData] s
inner join #NewAgreements t on s.InfoEdProposalNumber = t.foldernumber and s.FundID = t.FundID

drop table #NewAgreements

/*-----------------------------------------------------------------------------------
Map staging rows
-----------------------------------------------------------------------------------*/
update s set ArraAgreementID = a.ID from [dbo].[Stg_IE_ARRA_ProposalData] s
inner join [dbo].[ArraAgreement] a on a.foldernumber = s.InfoEdProposalNumber and a.FundID = s.FundID
inner join [dbo].[ArraQuarterlyReportStatus] qs on qs.ID = a.ArraQuarterlyReportStatusID 
							and qs.QuarterEndDate = @QuarterEndDate and qs.IsFinalReport = 0

update s set ArraActivityID = a.ID from [dbo].[Stg_IE_ARRA_ProposalData] s
inner join [dbo].[ArraActivity] a on a.code = s.ActivityCode

update s set FundingArraGovAgencyID = a.ID from [dbo].[Stg_IE_ARRA_ProposalData] s
inner join [dbo].[ArraGovAgency] a on a.code = s.FundingAgencyCode

update s set AwardingArraGovAgencyID = a.ID from [dbo].[Stg_IE_ARRA_ProposalData] s
inner join [dbo].[ArraGovAgency] a on a.code = s.AwardAgencyCode

update s set ArraGovContractOfficeID = a.ID from [dbo].[Stg_IE_ARRA_ProposalData] s
inner join [dbo].[ArraGovContractOffice] a on a.code = s.GovernmentContractOfficeCode

update s set ArraPrimaryPerformanceLocationID = a.ID From [dbo].[Stg_IE_ARRA_ProposalData] s
left outer join [dbo].[ArraPrimaryPerformanceLocation] a on coalesce(a.[StreetAddress1],'') = coalesce(s.PrimaryPerformanceStreet1,'')
						and IsNull(a.[StreetAddress2],'') = IsNull(s.PrimaryPerformanceStreet2,'')
						and a.[City] = s.PrimaryPerformanceCity and a.[State] = s.PrimaryPerformanceState
						and IsNull(a.[Zip],'') = IsNull(replace(s.PrimaryPerformanceZip,'-',''),'') 
						and IsNull(a.[Country],'') = IsNull(left(s.PrimaryPerformanceCountry,2),'')
where IsNull(a.[City],'')<>'' and isnull(s.PrimaryPerformanceCity,'')<>''

update s set ArraRecipientID = r.ID from [dbo].[Stg_IE_ARRA_ProposalData] s
inner join [dbo].[ArraRecipient] r on Replace(s.RecipientDunsNumber,'-','') = r.DunsNumber

update s set ArraProgramSourceID = p.ID from [dbo].[Stg_IE_ARRA_ProposalData] s
inner join [dbo].[ArraProgramSource] p on p.Code = s.ProgramSourceTas

/*-----------------------------------------------------------------------------------
Map SubAward ID columns
-----------------------------------------------------------------------------------*/
update s set ArraAgreementID = isnull(a.ArraAgreementID,a.NewArraAgreementID)
from [dbo].[Stg_IE_ARRA_SubContractorData] s
inner join [dbo].[Stg_IE_ARRA_ProposalData] a on a.InfoEdProposalNumber = s.InfoEdProposalNumber and a.PSAccountNumber = s.PSAccountNumber

update s set FinAccountID = a.ID from [dbo].[Stg_IE_ARRA_SubContractorData] s
inner join [dbo].[FinAccount] a on a.Number = s.SubGLAccount

update s set ArraSubAwardID = a.ID from [dbo].[Stg_IE_ARRA_SubContractorData] s
inner join [dbo].[ArraSubAward] a on a.ArraAgreementID = s.ArraAgreementID and a.FinAccountID = s.FinAccountID

update s set ArraSubPrimaryPerformanceLocationID = a.ID From [dbo].[Stg_IE_ARRA_SubContractorData] s
left outer join [dbo].[ArraPrimaryPerformanceLocation] a on IsNull(a.[StreetAddress1],'') = IsNull(s.SubPrimaryPerformanceAddress1,'')
						and IsNull(a.[StreetAddress2],'') = IsNull(s.SubPrimaryPerformanceAddress2,'')
						and a.[City] = s.SubPrimaryPerformanceCity and a.[State] = s.SubPrimaryPerformanceState
						and IsNull(a.[Zip],'') = IsNull(replace(s.SubPrimaryPerformanceZip,'-',''),'') 
						and IsNull(a.[Country],'') = IsNull(left(s.SubPrimaryPerformanceCountry,2),'')
where IsNull(a.[City],'')<>'' and isnull(s.SubPrimaryPerformanceCity,'')<>''

update s set ArraRecipientID = r.ID from [dbo].[Stg_IE_ARRA_SubContractorData] s
inner join [dbo].[ArraRecipient] r on replace(s.SubRecipientDunsNumber,'-','') = r.DunsNumber


/*-----------------------------------------------------------------------------------
Seed New ArraSubAwards in staging
-----------------------------------------------------------------------------------*/
if object_id('tempdb..#NewSubAwards') is not null drop table #NewSubAwards
Create table #NewSubAwards(ID int identity, ArraAgreementID int, FinAccountID int)

INSERT INTO #NewSubAwards(ArraAgreementID,FinAccountID)
Select distinct s.ArraAgreementID,s.FinAccountID
from [dbo].[Stg_IE_ARRA_SubContractorData] s
where s.ArraSubAwardID is null
									
set @MaxID = isnull((select max(ID) from [dbo].[ArraSubAward]),0)
Update s Set [NewArraSubAwardID] = @MaxID + t.ID
from [dbo].[Stg_IE_ARRA_SubContractorData] s
inner join #NewSubAwards t on s.ArraAgreementID = t.ArraAgreementID and s.FinAccountID = t.FinAccountID

drop table #NewSubAwards

End Try

Begin Catch
Declare 
    @ErrorMessage    NVARCHAR(4000),
    @ErrorNumber     INT,
    @ErrorSeverity   INT,
    @ErrorState      INT,
    @ErrorLine       INT,
    @ErrorProcedure  NVARCHAR(200);

-- Assign variables to error-handling functions that 
-- capture information for RAISERROR.
Select 
    @ErrorNumber = ERROR_NUMBER(),
    @ErrorSeverity = ERROR_SEVERITY(),
    @ErrorState = ERROR_STATE(),
    @ErrorLine = ERROR_LINE(),
    @ErrorProcedure = ISNULL(ERROR_PROCEDURE(), '-');

-- Building the message string that will contain original
-- error information.
Select @ErrorMessage = 
    N'Error %d, Level %d, State %d, Procedure %s, Line %d, ' + 
        'Message: '+ ERROR_MESSAGE();

--If XACT_STATE() <> 0 Rollback Transaction 

-- Raise an error: msg_str parameter of RAISERROR will contain
-- the original error information.
Raiserror
    (
    @ErrorMessage, 
    @ErrorSeverity, 
    1,               
    @ErrorNumber,    -- parameter: original error number.
    @ErrorSeverity,  -- parameter: original error severity.
    @ErrorState,     -- parameter: original error state.
    @ErrorProcedure, -- parameter: original error procedure name.
    @ErrorLine       -- parameter: original error line number.
    );
End Catch;

END 
GO 
